﻿namespace Chain.IDAL
{
    using Chain.DBUtility;
    using Chain.Model;
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Runtime.InteropServices;
    using System.Text;

    public class Message
    {
        public int Add(Chain.Model.Message model)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("insert into Message(");
            builder.Append("MessageMemID,MessageContent,MessageTime,MessageIsReply)");
            builder.Append(" values (");
            builder.Append("@MessageMemID,@MessageContent,@MessageTime,@MessageIsReply)");
            builder.Append(";select @@IDENTITY");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@MessageMemID", SqlDbType.Int, 4), new SqlParameter("@MessageContent", SqlDbType.NVarChar, 0xfa0), new SqlParameter("@MessageTime", SqlDbType.DateTime), new SqlParameter("@MessageIsReply", SqlDbType.Int, 4) };
            cmdParms[0].Value = model.MessageMemID;
            cmdParms[1].Value = model.MessageContent;
            cmdParms[2].Value = model.MessageTime;
            cmdParms[3].Value = model.MessageIsReply;
            if (DbHelperSQL.GetSingle(builder.ToString(), cmdParms) == null)
            {
                return 0;
            }
            return 1;
        }

        public bool Delete(int MessageID)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("delete from Message ");
            builder.Append(" where MessageID=@MessageID");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@MessageID", SqlDbType.Int, 4) };
            cmdParms[0].Value = MessageID;
            return (DbHelperSQL.ExecuteSql(builder.ToString(), cmdParms) > 0);
        }

        public bool DeleteList(string MessageIDlist)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("delete from Message ");
            builder.Append(" where MessageID in (" + MessageIDlist + ")  ");
            return (DbHelperSQL.ExecuteSql(builder.ToString()) > 0);
        }

        public bool Exists(int MessageID)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select count(1) from Message");
            builder.Append(" where MessageID=@MessageID");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@MessageID", SqlDbType.Int, 4) };
            cmdParms[0].Value = MessageID;
            return DbHelperSQL.Exists(builder.ToString(), cmdParms);
        }

        public DataSet GetList(string strWhere)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select MessageID,MessageMemID,MessageContent,MessageTime,MessageIsReply,MessageReplyContent,MessageReplyTime,MessageReplyUserID ");
            builder.Append(" FROM Message ");
            if (strWhere.Trim() != "")
            {
                builder.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(builder.ToString());
        }

        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select ");
            if (Top > 0)
            {
                builder.Append(" top " + Top.ToString());
            }
            builder.Append(" MessageID,MessageMemID,MessageContent,MessageTime,MessageIsReply,MessageReplyContent,MessageReplyTime,MessageReplyUserID ");
            builder.Append(" FROM Message ");
            if (strWhere.Trim() != "")
            {
                builder.Append(" where " + strWhere);
            }
            builder.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(builder.ToString());
        }

        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SELECT * FROM ( ");
            builder.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                builder.Append("order by T." + orderby);
            }
            else
            {
                builder.Append("order by T.MessageID desc");
            }
            builder.Append(")AS Row, T.*  from Message T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                builder.Append(" WHERE " + strWhere);
            }
            builder.Append(" ) TT");
            builder.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperSQL.Query(builder.ToString());
        }

        public DataSet GetListSP(int PageSize, int PageIndex, out int resCount, params string[] strWhere)
        {
            string tableName = "Message,Mem,SysShop";
            string[] column = new string[] { "MemID, MemCard,MemName,MemMobile,ShopName,avg(MessageIsReply) as MessageIsReply ,Max(MessageTime) as MessageTime, count(MessageID) as MessageCount" };
            string[] condition = strWhere;
            int recordCount = 1;
            DataSet set = DbHelperSQL.GetTable(tableName, column, condition, "MemID", true, PageSize, PageIndex, recordCount);
            try
            {
                resCount = int.Parse(set.Tables[1].Rows[0][0].ToString());
            }
            catch
            {
                resCount = 0;
            }
            return set;
        }

        public DataSet GetListSPInfo(int PageSize, int PageIndex, out int resCount, params string[] strWhere)
        {
            string tableName = " [Message],Mem ";
            string[] column = new string[] { " [Message].*,MemCard,MemName,(select UserName from SysUser where [Message].MessageReplyUserID=SysUser.UserID) as UserName " };
            string[] condition = strWhere;
            int recordCount = 1;
            DataSet set = DbHelperSQL.GetTable(tableName, column, condition, "MessageID", "MessageID", false, PageSize, PageIndex, recordCount);
            resCount = int.Parse(set.Tables[1].Rows[0][0].ToString());
            return set;
        }

        public int GetMaxId()
        {
            return DbHelperSQL.GetMaxID("MessageID", "Message");
        }

        public DataSet GetMemMessageList(int PageSize, int PageIndex, out int resCount, params string[] strWhere)
        {
            string tableName = " [Message] ";
            string[] column = new string[] { " [Message].* " };
            string[] condition = strWhere;
            int recordCount = 1;
            DataSet set = DbHelperSQL.GetTable(tableName, column, condition, "MessageID", "MessageID", false, PageSize, PageIndex, recordCount);
            resCount = int.Parse(set.Tables[1].Rows[0][0].ToString());
            return set;
        }

        public DataSet GetMessenger(string strWhere)
        {
            new DataSet();
            StringBuilder builder = new StringBuilder();
            builder.Append("select MemID, MemCard,MemName,MemMobile,ShopName,avg(MessageIsReply) as MessageIsReply ,Max(MessageTime) as MessageTime, count(MessageID) as MessageCount from Message,Mem,SysShop ");
            if (strWhere != "")
            {
                builder.Append(" where " + strWhere);
            }
            builder.Append(" group by MemCard,MemName,MemMobile,ShopName,MemID ");
            return DbHelperSQL.Query(builder.ToString());
        }

        public Chain.Model.Message GetModel(int MessageID)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select  top 1 MessageID,MessageMemID,MessageContent,MessageTime,MessageIsReply,MessageReplyContent,MessageReplyTime,MessageReplyUserID from Message ");
            builder.Append(" where MessageID=@MessageID");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@MessageID", SqlDbType.Int, 4) };
            cmdParms[0].Value = MessageID;
            Chain.Model.Message message = new Chain.Model.Message();
            DataSet set = DbHelperSQL.Query(builder.ToString(), cmdParms);
            if (set.Tables[0].Rows.Count <= 0)
            {
                return null;
            }
            if ((set.Tables[0].Rows[0]["MessageID"] != null) && (set.Tables[0].Rows[0]["MessageID"].ToString() != ""))
            {
                message.MessageID = int.Parse(set.Tables[0].Rows[0]["MessageID"].ToString());
            }
            if ((set.Tables[0].Rows[0]["MessageMemID"] != null) && (set.Tables[0].Rows[0]["MessageMemID"].ToString() != ""))
            {
                message.MessageMemID = int.Parse(set.Tables[0].Rows[0]["MessageMemID"].ToString());
            }
            if ((set.Tables[0].Rows[0]["MessageContent"] != null) && (set.Tables[0].Rows[0]["MessageContent"].ToString() != ""))
            {
                message.MessageContent = set.Tables[0].Rows[0]["MessageContent"].ToString();
            }
            if ((set.Tables[0].Rows[0]["MessageTime"] != null) && (set.Tables[0].Rows[0]["MessageTime"].ToString() != ""))
            {
                message.MessageTime = DateTime.Parse(set.Tables[0].Rows[0]["MessageTime"].ToString());
            }
            if ((set.Tables[0].Rows[0]["MessageIsReply"] != null) && (set.Tables[0].Rows[0]["MessageIsReply"].ToString() != ""))
            {
                message.MessageIsReply = int.Parse(set.Tables[0].Rows[0]["MessageIsReply"].ToString());
            }
            if ((set.Tables[0].Rows[0]["MessageReplyContent"] != null) && (set.Tables[0].Rows[0]["MessageReplyContent"].ToString() != ""))
            {
                message.MessageReplyContent = set.Tables[0].Rows[0]["MessageReplyContent"].ToString();
            }
            if ((set.Tables[0].Rows[0]["MessageReplyTime"] != null) && (set.Tables[0].Rows[0]["MessageReplyTime"].ToString() != ""))
            {
                message.MessageReplyTime = DateTime.Parse(set.Tables[0].Rows[0]["MessageReplyTime"].ToString());
            }
            if ((set.Tables[0].Rows[0]["MessageReplyUserID"] != null) && (set.Tables[0].Rows[0]["MessageReplyUserID"].ToString() != ""))
            {
                message.MessageReplyUserID = int.Parse(set.Tables[0].Rows[0]["MessageReplyUserID"].ToString());
            }
            return message;
        }

        public int GetRecordCount(string strWhere)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select count(1) FROM Message ");
            if (strWhere.Trim() != "")
            {
                builder.Append(" where " + strWhere);
            }
            object single = DbHelperSQL.GetSingle(builder.ToString());
            if (single == null)
            {
                return 0;
            }
            return Convert.ToInt32(single);
        }

        public int MessageDel(int intMemID)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("delete from Message ");
            builder.Append(" where MessageMemID=@MessageMemID");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@MessageMemID", SqlDbType.Int, 4) };
            cmdParms[0].Value = intMemID;
            object obj2 = DbHelperSQL.ExecuteSql(builder.ToString(), cmdParms);
            if (obj2 == null)
            {
                return 0;
            }
            return Convert.ToInt32(obj2);
        }

        public bool Reply(Chain.Model.Message modelMessage)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(" update Message set MessageReplyContent='{0}', MessageReplyTime='{1}', MessageReplyUserID={2}, MessageIsReply={3} ", new object[] { modelMessage.MessageReplyContent, modelMessage.MessageReplyTime, modelMessage.MessageReplyUserID, modelMessage.MessageIsReply });
            builder.AppendFormat(" where MessageID={0}", modelMessage.MessageID);
            if (DbHelperSQL.ExecuteSql(builder.ToString()) <= 0)
            {
                return false;
            }
            return true;
        }

        public bool Update(Chain.Model.Message model)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("update Message set ");
            builder.Append("MessageMemID=@MessageMemID,");
            builder.Append("MessageContent=@MessageContent,");
            builder.Append("MessageTime=@MessageTime,");
            builder.Append("MessageIsReply=@MessageIsReply,");
            builder.Append("MessageReplyContent=@MessageReplyContent,");
            builder.Append("MessageReplyTime=@MessageReplyTime,");
            builder.Append("MessageReplyUserID=@MessageReplyUserID");
            builder.Append(" where MessageID=@MessageID");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@MessageMemID", SqlDbType.Int, 4), new SqlParameter("@MessageContent", SqlDbType.NVarChar, 0xfa0), new SqlParameter("@MessageTime", SqlDbType.DateTime), new SqlParameter("@MessageIsReply", SqlDbType.Int, 4), new SqlParameter("@MessageReplyContent", SqlDbType.NVarChar, 0xfa0), new SqlParameter("@MessageReplyTime", SqlDbType.DateTime), new SqlParameter("@MessageReplyUserID", SqlDbType.Int, 4), new SqlParameter("@MessageID", SqlDbType.Int, 4) };
            cmdParms[0].Value = model.MessageMemID;
            cmdParms[1].Value = model.MessageContent;
            cmdParms[2].Value = model.MessageTime;
            cmdParms[3].Value = model.MessageIsReply;
            cmdParms[4].Value = model.MessageReplyContent;
            cmdParms[5].Value = model.MessageReplyTime;
            cmdParms[6].Value = model.MessageReplyUserID;
            cmdParms[7].Value = model.MessageID;
            return (DbHelperSQL.ExecuteSql(builder.ToString(), cmdParms) > 0);
        }
    }
}

